package com.telsoft.hms.data;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.telsoft.hms.entity.Patient;
import com.telsoft.hms.lib.admin.client.data.ClientDataPreprocessor;
import com.telsoft.hms.lib.security.exception.UserNotExistsException;
import com.telsoft.hms.util.SessionUtil;
import com.telsoft.hms.util.StringUtil;

public class Patient_DATA extends ClientDataPreprocessor {
	/**
	 * @author NinhNV
	 * @param strKey
	 *            Sercurity key
	 * @param username
	 *            Tên đăng nhập
	 * @param start
	 *            Từ ngày
	 * @param end
	 *            Đến ngày
	 * @param patientCode
	 *            Mã bệnh nhân
	 * @param fullName
	 *            Tên bệnh nhân
	 * @param idNo
	 *            Số CMND
	 * @param passportNo
	 *            Số Hộ chiếu
	 * @param pin
	 *            Số an sinh
	 * @param phone
	 *            SĐT
	 * @param email
	 *            Email
	 * @param address
	 *            Địa chỉ
	 * @param hieNo
	 *            Số bảo hiểm
	 * @return Danh sách bệnh nhân
	 * @throws Exception
	 *             Ngày tạo 15/09/2014
	 */
	public Patient[] searchPatient(String strKey, Date start, Date end,
			String patientCode, String fullName, String idNo,
			String passportNo, String pin, String phone, String email,
			String address, String hieNo) throws Exception {
		String hospitalKey = null;
		DateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
		List<Patient> patients = new ArrayList<Patient>();
		try {
			hospitalKey = SessionUtil.getSession(strKey).getHospitalKey();
		} catch (Exception e) {
			throw e;
		}
		StringBuilder sbWhere = new StringBuilder();
		sbWhere.append("SELECT p.PATIENT_ID, p.PATIENT_CODE,p.FULL_NAME,p.ID_NO,p.BIRTHDAY,p.ADDRESS,p.PHONE,p.MOBILE,p.PASSPORT_NO,p.PIN,P.SEX,t.HIE_NO,r.REGISTRATION_ID "
				+ "FROM PATIENT p "
				+ "INNER JOIN REGISTRATION r ON p.PATIENT_ID = r.PATIENT_ID "
				+ "INNER JOIN TREATMENT t ON t.REGISTRATION_ID = r.REGISTRATION_ID WHERE 1=1 ");
		if (start != null) {
			sbWhere.append(" AND p.CREATE_DATE >= TO_DATE('"
					+ format.format(start) + "','dd/MM/yyyy hh24:mi:ss')");
		}
		if (end != null) {
			sbWhere.append(" AND p.CREATE_DATE <= TO_DATE('"
					+ format.format(end) + "','dd/MM/yyyy hh24:mi:ss') +1");
		}
		if (!StringUtil.isNullOrEmpty(patientCode)) {
			sbWhere.append(" AND LOWER(p.PATIENT_CODE) LIKE LOWER('%"
					+ patientCode + "%')");
		}
		if (!StringUtil.isNullOrEmpty(fullName)) {
			sbWhere.append(" AND LOWER(p.FULL_NAME) LIKE LOWER('%" + fullName
					+ "%')");
		}
		if (!StringUtil.isNullOrEmpty(idNo)) {
			sbWhere.append(" AND LOWER(p.ID_NO) LIKE LOWER('%" + idNo + "%')");
		}
		if (!StringUtil.isNullOrEmpty(passportNo)) {
			sbWhere.append(" AND LOWER(p.PASSPORT_NO) LIKE LOWER('%"
					+ passportNo + "%')");
		}
		if (!StringUtil.isNullOrEmpty(pin)) {
			sbWhere.append(" AND LOWER(p.PIN) LIKE LOWER('%" + pin + "%')");
		}
		if (!StringUtil.isNullOrEmpty(phone)) {
			sbWhere.append(" AND (LOWER(p.PHONE) LIKE LOWER('%" + phone
					+ "%') OR LOWER(p.MOBILE) LIKE LOWER('%" + phone + "%'))");
		}
		if (!StringUtil.isNullOrEmpty(email)) {
			sbWhere.append(" AND LOWER(p.EMAIL) LIKE LOWER('%" + email + "%')");
		}
		if (!StringUtil.isNullOrEmpty(address)) {
			sbWhere.append(" AND LOWER(p.ADDRESS) LIKE LOWER('%" + address
					+ "%')");
		}
		if (!StringUtil.isNullOrEmpty(hieNo)) {
			sbWhere.append(" AND LOWER(t.HIE_NO) LIKE LOWER('%" + hieNo + "%')");
		}
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sbWhere.toString());
			mRs = mStmt.executeQuery();
			while (mRs.next()) {
				Patient patient = new Patient();
				patient.setPatientCode(mRs.getString("PATIENT_CODE"));
				patient.setFullName(mRs.getString("FULL_NAME"));
				patient.setIdNo(mRs.getString("ID_NO"));
				patient.setBirthday(mRs.getDate("BIRTHDAY"));
				patient.setAddress(mRs.getString("ADDRESS"));
				patient.setPhone(mRs.getString("PHONE"));
				patient.setPassportNo(mRs.getString("PASSPORT_NO"));
				patient.setPin(mRs.getString("PIN"));
				patient.setMobile(mRs.getString("MOBILE"));
				patient.setSex(mRs.getString("SEX"));
				patient.setHieNo(mRs.getString("HIE_NO"));
				patient.setRegistrationID(mRs.getLong("REGISTRATION_ID"));
				patient.setPatientId(mRs.getLong("PATIENT_ID"));
				patients.add(patient);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			close(mRs);
			close(mStmt);
			close(mConnection);
		}

		return (Patient[]) patients.toArray(new Patient[patients.size()]);
	}
}
